import MySQLdb 
import MySQLdb.cursors
import pandas as pd
import numpy as np  
import scipy as sp  
import matplotlib.pyplot as plt 
import requests
import json
from requests import get,post,Session
import time
import datetime
from apscheduler.schedulers.background import BackgroundScheduler
from sqlalchemy import create_engine
import os

# 连接数据库 
def connectdb(): 
       db = MySQLdb.connect(host="39.108.8.103", user="root", passwd="admin", db="tradedb", port=3306) 
       db.autocommit(True) 
       cursor = db.cursor() 
       return db,cursor
 
def createcon():
    return create_engine('mysql+mysqldb://root:admin@39.108.8.103:3306/tradedb?charset=utf8') 
 
# 关闭数据库 
def closedb(db,cursor): 
       db.close() 
       cursor.close()



def query():
        data = {"futuresType":"CL"}
        r = requests.post("http://stock.oksdb.com/futuresquota/getCacheData",data)
        j = json.loads(r.text)
        return j["data"]["lastPrice"],j["data"]["percentage"],j["data"]["code"]

def job():
       a = query()
       dat = {}
       dat["price"]=float(a[0])
       dat["code"]=a[2]
       dat["datetime"]=datetime.datetime.now().strftime('%Y.%m.%d %H:%M:%S');
       df = pd.DataFrame(data=dat,index=range(1))
       df.to_sql("witoil",con,if_exists='append',index = False)
       return df
       
if __name__ == '__main__':
       con = createcon()
       scheduler = BackgroundScheduler()
       scheduler.add_job(job, 'interval', seconds=1)#间隔3秒钟执行一次
       scheduler.start()    #这里的调度任务是独立的一个线程
       print('Press Ctrl+{0} to exit'.format('Break' if os.name == 'nt' else 'C'))
       
       try:
       # This is here to simulate application activity (which keeps the main thread alive).
              while True:
                     time.sleep(2)    #其他任务是独立的线程执行
       except (KeyboardInterrupt, SystemExit):
       # Not strictly necessary if daemonic mode is enabled but should be done if possible
              scheduler.shutdown()
              print('Exit The Job!')



       
